/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.controller;

import com.db.Conector;
import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Inscrito;
import com.model.InscritoManager;
import com.model.ManagerFactory;
import com.util.UtilDate;
import com.util.UtilTransaccion;
import com.util.setNotification;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FilenameUtils;

/**
 *
 * @author Administrador
 */
public class Upload extends HttpServlet {

    
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, DataException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        
        Connection c = null;
        
        List fileItemsList = null;
        float filesize = 0;
        String _fileLink;
        String _fileName = null;
        String _uploadDir = getServletContext().getRealPath("/upload/").replace("\\build","");;
        
        HttpSession session = request.getSession(true);
        try {
            c = Conector.getConexion();
            InscritoManager iManager = ManagerFactory.getInscritoManagerInstance(c);
            
            if (ServletFileUpload.isMultipartContent(request)) {
                ServletFileUpload servletFileUpload = new ServletFileUpload(new DiskFileItemFactory());
                try {
                    fileItemsList = servletFileUpload.parseRequest(request);
                } catch (FileUploadException ex) {
                    Logger.getLogger(File.class.getName()).log(Level.SEVERE, null, ex);                   
                }
                String optionalFileName = "";
                FileItem fileItem = null;

                Iterator it = fileItemsList.iterator();
                while (it.hasNext()) {
                FileItem fileItemTemp = (FileItem) it.next();
                    if (fileItemTemp.isFormField()) {
                    if (fileItemTemp.getFieldName().equals("filename")) {
                        optionalFileName = fileItemTemp.getString();
                    }
                    
                    } else {
                        fileItem = fileItemTemp;
                    }
                }
                if (fileItem != null) {
                    long size_long = fileItem.getSize();
                    filesize = size_long / 1024;
                    filesize = filesize / 1000;                    
                    if (filesize > 30.0) {                    
                    setNotification _sN = new setNotification();
                    _sN.setError("File size can't be more than 30MB");
                    session.setAttribute("error", _sN);
                    } else {
                        _fileName = fileItem.getName();
                        if (fileItem.getSize() > 0) {
                            if (optionalFileName.trim().equals("")) {
                                _fileName = FilenameUtils.getName(_fileName);
                            } else {
                                _fileName = optionalFileName;
                            }
                            _fileLink = "../upload/" + _fileName;
                            try {
                                //para  concatenar la fecha
                                DateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
                                String res = dateFormat.format(Calendar.getInstance().getTime());
                                
                                File file = new File(new File(_uploadDir + "/"), res+fileItem.getName());
                                fileItem.write(file);
                                
                                // ===========================READ FILE===========================
                                // ahora quiero leer lo q se guardo en el servidor
                                String readFile = file+"";
                                //Creamos un Workbook para cargar el XLS en memoria 
                                Workbook workbook = Workbook.getWorkbook(new File(readFile));
                                //Elegimos la primera hoja
                                Sheet sheet = workbook.getSheet(0);
                                //inicializo el objeto que leerá el valor de la celda
                                Cell celdaCurso = null;
                                //Este String guardará el valor de la celda
                                String valorCeldaCurso=null;

                                //Obtengo el número de filas ocupadas en la hoja
                                int rows=sheet.getRows();
                                //Obtengo el número de columnas ocupadas en la hoja
                                int cols=sheet.getColumns();              
                                
                                // inicializamos es objeto para guardarlo en una lista
                                List<Inscrito> inscritos = new ArrayList<Inscrito>();
                                
                                int aux = 0;
                                int idGestion = 0;
                                int idGrupo = 0;
                                int idPensum = 0;
                                int idCarrera = 0;
                                int idMateria = 0;
                                
                                int idEstudiante = 0;
                                String fecha = "";
                                int t1 = 0;
                                int t2 = 0;
                                int t3 = 0;
                                int nota = 0;
                                int seg = 0;
                                int nf = 0;
                                boolean aprovado = false;
                                
                                boolean flag = true;
                                boolean flag2 = false;
                                boolean flag3 = false;
                                String aux2 = "";
                                //Para efectos de ejemplo recorremos las columnas de cada fila
                                for(int x=0;x<rows;x++){
                                    
                                    if( x>=2){
                                        for(int y=0; y<cols; y++){
                                            //Obtenemos el valor de la celda de la columna Y y fila X
                                            celdaCurso= sheet.getCell(y,x);
                                            //Obtenemos el valor de la celda
                                            valorCeldaCurso = celdaCurso.getContents().trim();
                                            if(flag){
                                                if(y == 1 && x==2){
                                                    idGestion = Integer.parseInt(valorCeldaCurso);                                                   
                                                }else if( y==1 && x==3){
                                                    idGrupo = Integer.parseInt(valorCeldaCurso);
                                                }else if( y==1 && x==4){
                                                    idPensum = Integer.parseInt(valorCeldaCurso);
                                                }else if( y==1 && x==5){
                                                    idCarrera = Integer.parseInt(valorCeldaCurso);
                                                }else if( y==1 && x==6){
                                                    idMateria = Integer.parseInt(valorCeldaCurso);
                                                    flag = false;
                                                }
                                            }
                                            if(flag2 && !valorCeldaCurso.equals("")){
                                                switch(y){
                                                    case 1:
                                                        idEstudiante = Integer.parseInt(valorCeldaCurso);
                                                        break;
                                                    case 6:
                                                        t1 = Integer.parseInt(valorCeldaCurso);
                                                        break;
                                                    case 7:
                                                        t2 = Integer.parseInt(valorCeldaCurso);
                                                        break;
                                                    case 8:
                                                        t3 = Integer.parseInt(valorCeldaCurso);
                                                        break;
                                                    case 9:
                                                        seg = Integer.parseInt(valorCeldaCurso);
                                                        break;
                                                }
                                            }
                                            if(x == 8){
                                                flag2 = true;
                                            }
                                        }//for
                                        if(x >= 8 && idEstudiante != 0){//el igual a cero es porq al exportar tenemos una fila con espacio
                                            Inscrito ins = new Inscrito();
                                            ins.setIdGestion(idGestion);
                                            ins.setIdGrupo(idGrupo);
                                            ins.setIdPensum(idPensum);
                                            ins.setIdCarrera(idCarrera);
                                            ins.setIdMateria(idMateria);
                                            ins.setIdEstudiante( idEstudiante );
                                            ins.setT1( t1 );
                                            ins.setT2( t2 );
                                            ins.setT3( t3 );
                                            ins.setNota(  (int)Math.round(((float)(ins.getT1()+ins.getT2()+ins.getT3())/3)  ) );
                                            if(ins.getNota() >=61 ){
                                                ins.setNf( ins.getNota() );
                                                ins.setSeg2( 0 );
                                            }else{
                                                if(seg >= 61){
                                                    ins.setSeg2(61);
                                                    ins.setNf(61);
                                                }else{
                                                    ins.setSeg2(seg);
                                                    ins.setNf(seg);
                                                }
                                            }
                                            if(ins.getNota() >= 61 || ins.getSeg2() >= 61){
                                                ins.setAprobado(true);
                                            }else{
                                                ins.setAprobado(false);
                                            }
                                            iManager.updateNotasXLS(ins);                                            
                                            idEstudiante = t1 = t2 = t3 = seg = 0;
                                        }
                                    }// if
                                    
                                    aux2 = "";
                               }// for
                              
                                workbook.close();
                                // ===========================END FILE===========================
                                
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            setNotification _sN = new setNotification();
                            _sN.setError("Archivo cargado a : " + _fileLink + "");
                            session.setAttribute("error", _sN);
                        }
                    }
                }
                String referer = request.getHeader("Referer");
                response.sendRedirect(referer);
            }
        }catch(Exception ex){
            System.out.println(UtilDate.getDateTime()+" Administracion -> showHorarios() ex: "+ex.getMessage());
            request.setAttribute("msgError", UtilDate.getDateTime()+" Administracion -> showHorarios() Exception ex: "+ex.getMessage());
            //PAGE = ERROR_PAGE;
        }finally{
            SqlManager.closeConexion(c);
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (DataException ex) {
            Logger.getLogger(Upload.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (DataException ex) {
            Logger.getLogger(Upload.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
